package com.ccjq.shosestore.product.dao.impl;

import com.ccjq.shosestore.core.dbmanager.DbConnection;
import com.ccjq.shosestore.product.dao.ProductDao;
import com.ccjq.shosestore.product.model.BrandModel;
import com.ccjq.shosestore.product.model.ProductModel;
import com.ccjq.shosestore.product.util.GetList;
import com.ccjq.shosestore.product.util.GetScrollList;
import com.ccjq.shosestore.product.util.PageBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class ProductDaoImpl implements ProductDao {

	public List<ProductModel> queryAll(int pageSize, int startRow) {
		String s = "SELECT * FROM (SELECT PRODUCT_ID, PRICE, DESCRIPTION, MODEL, FEATURE, IMGPATH, NOVALTY_STATUS, PUBLISH_DATE, CATEGORY_ID, ROWNUM as n  FROM (SELECT PRODUCT_ID, PRICE, DESCRIPTION, MODEL, FEATURE, IMGPATH, NOVALTY_STATUS, PUBLISH_DATE, CATEGORY_ID FROM T_PRODUCT ORDER BY PUBLISH_DATE DESC))  WHERE n BETWEEN ? and ? ";
		List<ProductModel> productlist = new ArrayList<ProductModel>();
		Connection conn = DbConnection.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(s);
			ps.setInt(1, startRow);
			ps.setInt(2, pageSize + startRow - 1);
			ResultSet re = ps.executeQuery();

			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setCategory_id(re.getString(9));
				po.setPrice(re.getString(2));
				po.setDescription(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setProduct_id(re.getString(1));
				productlist.add(po);
			}
			DbConnection.close(re, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return productlist;
	}

	public List<ProductModel> queryAll(String categoryId, int pageSize, int startRow) {
		String s = "SELECT * FROM (SELECT PRODUCT_ID, PRICE, DESCRIPTION, MODEL, FEATURE, IMGPATH, NOVALTY_STATUS, PUBLISH_DATE, CATEGORY_ID, ROWNUM as n  FROM (SELECT PRODUCT_ID, PRICE, DESCRIPTION, MODEL, FEATURE, IMGPATH, NOVALTY_STATUS, PUBLISH_DATE, CATEGORY_ID FROM T_PRODUCT WHERE CATEGORY_ID=? ORDER BY PUBLISH_DATE DESC))  WHERE n BETWEEN ? and ? ";

		List<ProductModel> productlist = new ArrayList<ProductModel>();
		Connection conn = DbConnection.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(s);
			ps.setString(1, categoryId);
			ps.setInt(2, startRow);
			ps.setInt(3, pageSize + startRow - 1);

			ResultSet re = ps.executeQuery();

			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setCategory_id(re.getString(9));
				po.setPrice(re.getString(2));
				po.setDescription(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setProduct_id(re.getString(1));
				productlist.add(po);
			}
			DbConnection.close(re, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return productlist;
	}


	public List<ProductModel> getScrollList() {
		String s = "SELECT SCOTT.\"T_SLIDER\".\"DESCRIPTION\", SCOTT.\"T_SLIDER\".\"PRODUCT_ID\", SCOTT.\"T_SLIDER\".\"IMGPATH\", SCOTT.\"T_SLIDER\".\"AVAILABILITY\" FROM SCOTT.\"T_SLIDER\" WHERE SCOTT.\"T_SLIDER\".\"AVAILABILITY\" > 0  ";
		System.out.println("getScrollList SQL: " + s);
		GetScrollList getList = new GetScrollList();
		List<ProductModel> products = getList.GetList(s);
		return products;
	}

	// Category_id,currentPage,pageMethod, totalRows

	public List<ProductModel> getBrand(String Category_id, int startRow, int pageSize) {
		String sql = "SELECT * FROM T_PRODUCT WHERE \"CATEGORY_ID\"='" + Category_id + "'";
		GetList getList = new GetList();
		pageSize = PageBean.getPageSize();
		// 拿到所有数据的list
		List<ProductModel> products = getList.GetList(sql);
		ProductDao p = new ProductDaoImpl();
		// 开始处理list，
		// 判断最后一页数量是否超数 并截取对应的list 返回
		if ((pageSize + startRow - 1) <= products.size()) {
			// 讲道理 最后一项的下标不大于最后一个数据的下标
			products = products.subList((startRow - 1), pageSize + startRow - 1);
		} else if ((pageSize + startRow - 1) > products.size()) {
			// 不讲道理 最后一项的下标超过了最后一个数据的下标
			products = products.subList((startRow - 1), products.size());
		}

		// 返回请求页数的list
		return products;

	}


	public List<ProductModel> getProductList(int startRow, int pageSize) {
		String s = "SELECT SCOTT.T_PRODUCT.\"PRODUCT_ID\", SCOTT.T_PRODUCT.\"PRICE\", SCOTT.T_PRODUCT.\"DESCRIPTION\", SCOTT.T_PRODUCT.\"MODEL\", SCOTT.T_PRODUCT.\"FEATURE\", SCOTT.T_PRODUCT.\"IMGPATH\", SCOTT.T_PRODUCT.\"NOVALTY_STATUS\", SCOTT.T_PRODUCT.\"PUBLISH_DATE\", SCOTT.T_PRODUCT.\"CATEGORY_ID\" FROM SCOTT.T_PRODUCT WHERE  PRODUCT_ID IN (SELECT PRODUCT_ID FROM  SCOTT.T_STOCK WHERE SCOTT.T_STOCK.\"QUANTITY\" > 0) ";
		System.out.println(s);
		pageSize = PageBean.getPageSize();
		GetList getList = new GetList();
		// 拿到所有数据的list
		List<ProductModel> products = getList.GetList(s);
		ProductDao p = new ProductDaoImpl();
		// 开始处理list，
		// 判断最后一页数量是否超数 并截取对应的list 返回
		if ((pageSize + startRow - 1) <= products.size()) {
			System.out.println((pageSize + startRow - 1) + " / " + products.size());
			// 讲道理 最后一项的下标不大于最后一个数据的下标
			products = products.subList((startRow - 1), pageSize + startRow - 1);

		} else if ((pageSize + startRow - 1) > products.size()) {
			System.out.println((pageSize + startRow - 1) + " / " + products.size());
			// 不讲道理 最后一项的下标超过了最后一个数据的下标
			products = products.subList((startRow - 1), products.size());
		}
		// 返回请求页数的list
		return products;
	}

	public int getBrandRow(String Category_id) {
		int row = 0;
		try {
			String s = "SELECT COUNT(*) FROM T_PRODUCT WHERE \"CATEGORY_ID\"='" + Category_id + "'";
			System.out.println("getBrandRow SQL:" + s);
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				row = re.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;

	}


	public List<ProductModel> getFashionList() {
		String s = "SELECT SCOTT.T_PRODUCT.\"PRODUCT_ID\", SCOTT.T_PRODUCT.\"PRICE\", SCOTT.T_PRODUCT.\"DESCRIPTION\", SCOTT.T_PRODUCT.\"MODEL\", SCOTT.T_PRODUCT.\"FEATURE\", SCOTT.T_PRODUCT.\"IMGPATH\", SCOTT.T_PRODUCT.\"NOVALTY_STATUS\", SCOTT.T_PRODUCT.\"PUBLISH_DATE\", SCOTT.T_PRODUCT.\"CATEGORY_ID\" FROM SCOTT.T_PRODUCT WHERE SCOTT.T_PRODUCT.\"PRODUCT_ID\" IN (SELECT \"PRODUCT_ID\" FROM (SELECT SUM(SCOTT.T_ORDER_PRODUCT.\"QUANTITY\"), SCOTT.T_ORDER_PRODUCT.\"PRODUCT_ID\" FROM SCOTT.T_ORDER_PRODUCT GROUP BY SCOTT.T_ORDER_PRODUCT.\"PRODUCT_ID\" ORDER BY SUM( SCOTT.T_ORDER_PRODUCT.\"QUANTITY\" ) DESC)  WHERE ROWNUM<5)  ";
		GetList getList = new GetList();
		List<ProductModel> products = getList.GetList(s);
		System.out.print("getFashionList SQL：" + s);
		return products;
	}


	public List<ProductModel> getNewproductList() {
		// String s =
		// "SELECT	SCOTT.T_PRODUCT.\"PRODUCT_ID\",	SCOTT.T_PRODUCT.\"PRICE\",	SCOTT.T_PRODUCT.\"DESCRIPTION\",	SCOTT.T_PRODUCT.\"MODEL\",	SCOTT.T_PRODUCT.\"FEATURE \",	SCOTT.T_PRODUCT.\"IMGPATH\",	SCOTT.T_PRODUCT.\"NOVALTY_STATUS\",	SCOTT.T_PRODUCT.\"PUBLISH_DATE\",	SCOTT.T_PRODUCT.\"CATEGORY_ID\",	SCOTT.T_STOCK.\"QUANTITY\" FROM	SCOTT.T_PRODUCT,	SCOTT.T_STOCK WHERE	SCOTT.T_STOCK.\"QUANTITY\" > 0 	AND ROWNUM <= 6 ORDER BY	SCOTT.T_PRODUCT.\"PUBLISH_DATE\" DESC";
		String s = "SELECT * FROM ( SELECT * FROM SCOTT.T_PRODUCT ORDER BY SCOTT.T_PRODUCT.PUBLISH_DATE DESC ) WHERE ROWNUM <= 6 ";
		GetList getList = new GetList();
		List<ProductModel> products = getList.GetList(s);
		System.out.println("getNewproductList SQL：" + s);
		return products;
	}


	public ProductModel getProductText(String product_id) {
		String s = "SELECT SCOTT.T_PRODUCT.\"PRODUCT_ID\", SCOTT.T_PRODUCT.\"PRICE\", SCOTT.T_PRODUCT.\"DESCRIPTION\", SCOTT.T_PRODUCT.\"MODEL\", SCOTT.T_PRODUCT.\"FEATURE\", SCOTT.T_PRODUCT.\"IMGPATH\", SCOTT.T_PRODUCT.\"NOVALTY_STATUS\", SCOTT.T_PRODUCT.\"PUBLISH_DATE\", SCOTT.T_PRODUCT.\"CATEGORY_ID\" FROM SCOTT.T_PRODUCT WHERE SCOTT.T_PRODUCT.\"PRODUCT_ID\" ='"

		+ product_id + "' ";

		GetList getList = new GetList();
		ProductModel productText = new ProductModel();
		List<ProductModel> products = getList.GetList(s);
		for (ProductModel p : products) {
			productText.setCategory_id(p.getCategory_id());
			productText.setPrice(p.getPrice());
			productText.setDescription(p.getDescription());
			productText.setModel(p.getModel());
			productText.setFeature(p.getFeature());
			productText.setImgpath(p.getImgpath());
			productText.setNovalty_date(p.getNovalty_date());
			productText.setPublish_date(p.getPublish_date());
			productText.setProduct_id(p.getProduct_id());
		}
		return productText;
	}


	public int getQuantity(String product_id) {

		int quantity = 0;
		try {

			String s = "SELECT SCOTT.T_STOCK.\"QUANTITY\" FROM SCOTT.T_STOCK WHERE SCOTT.T_STOCK.\"PRODUCT_ID\" = '"

			+ product_id + "'  ";

			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);

			while (re.next()) {
				quantity = re.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return quantity;
	}


	public String getName(String ategory_id) {

		String name = null;
		try {
			String s = "SELECT	SCOTT.T_CATEGORY.\"NAME\" FROM	SCOTT.T_CATEGORY WHERE	SCOTT.T_CATEGORY.\"CATEGORY_ID\" = '" + ategory_id + "'";
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);

			while (re.next()) {
				name = re.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return name;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ccjq.shosestore.product.dao.ProductDao#getBrowseList()
	 */

	public List<ProductModel> getBrowseList() {
		String s = "SELECT SCOTT.T_PRODUCT.\"PRODUCT_ID\", SCOTT.T_PRODUCT.\"PRICE\", SCOTT.T_PRODUCT.\"DESCRIPTION\", SCOTT.T_PRODUCT.\"MODEL\", SCOTT.T_PRODUCT.\"FEATURE\", SCOTT.T_PRODUCT.\"IMGPATH\", SCOTT.T_PRODUCT.\"NOVALTY_STATUS\", SCOTT.T_PRODUCT.\"PUBLISH_DATE\", SCOTT.T_PRODUCT.\"CATEGORY_ID\" FROM SCOTT.T_PRODUCT WHERE SCOTT.T_PRODUCT.\"PRODUCT_ID\" IN (SELECT \"PRODUCT_ID\" FROM (SELECT SUM(SCOTT.T_ORDER_PRODUCT.\"QUANTITY\"), SCOTT.T_ORDER_PRODUCT.\"PRODUCT_ID\" FROM SCOTT.T_ORDER_PRODUCT GROUP BY SCOTT.T_ORDER_PRODUCT.\"PRODUCT_ID\" ORDER BY SUM( SCOTT.T_ORDER_PRODUCT.\"QUANTITY\" ) DESC)  WHERE ROWNUM<4)  ";

		GetList getList = new GetList();
		List<ProductModel> products = getList.GetList(s);
		System.out.print("getBrowseList SQL： " + s);
		return products;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ccjq.shosestore.product.dao.ProductDao#getProductListRow()
	 */

	public int getProductListRow() {
		int row = 0;

		try {
			String s = " SELECT COUNT(*)  FROM 	SCOTT.T_PRODUCT  WHERE 	PRODUCT_ID IN ( SELECT PRODUCT_ID FROM SCOTT.T_STOCK WHERE SCOTT.T_STOCK.QUANTITY > 0 )  ";
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				row = re.getInt(1);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

	public String getStock(String id) {
		String s = "SELECT  SUM(SCOTT.T_ORDER_PRODUCT.QUANTITY) FROM SCOTT.T_ORDER_PRODUCT WHERE SCOTT.T_ORDER_PRODUCT.PRODUCT_ID='" + id + "' ";
		Connection conn = DbConnection.getConnection();
		Statement stat;
		String stock = "";
		try {
			stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				stock = re.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return stock;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ccjq.shosestore.product.dao.ProductDao#br()
	 */

	public List<BrandModel> getBrandModel() {
		String s = "SELECT SCOTT.T_CATEGORY.CATEGORY_ID, SCOTT.T_CATEGORY.NAME FROM SCOTT.T_CATEGORY ";
		Connection conn = DbConnection.getConnection();
		List<BrandModel> brandlist = new ArrayList<BrandModel>();

		try {
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);

			while (re.next()) {
				BrandModel brand = new BrandModel();
				brand.setCategory_id(re.getString(1));
				brand.setName(re.getString(2));
				brandlist.add(brand);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return brandlist;
	}

}
